package dbutilsDemo.dbpoolDemo.d2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.dbutils.DbUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class DBJdbcRunners {
	private static Logger log = LoggerFactory.getLogger(DBJdbcRunners.class);
	private boolean autoCommit;
	Connection connection = null;

	public DBJdbcRunners() throws Exception {
		autoCommit = true;
		try {
			connection = C3p0Utils.getConn();
		} catch (Exception e) {
			throw new Exception();
		}
	}
	public void begintx()throws SQLException{
		autoCommit = false;
		if (connection != null){
			try {
				connection.setAutoCommit(false);
			} catch (SQLException e) {
				throw new SQLException();
			}
		}
	}
	public void committx()throws SQLException{
		autoCommit = false;
		if (connection != null){
			try {
				connection.commit();
				connection.setAutoCommit(true);
				autoCommit = true;
			} catch (SQLException e) {
				log.equals("committx exception is ->["+e+"]");
				throw new SQLException();
			}finally{
				DbUtils.close(connection);
			}
		}
	}

	public int executeUpdate (String sql) throws Exception
	{
		Statement statement = null;
		try
		{

			statement = connection.createStatement();
			int i = statement.executeUpdate(sql);
			return i;
		}catch( SQLException e){
			log.error("error sql=" + sql);
			log.error("",e);
			if(!autoCommit){
				try{
					connection.rollback();
				}catch(SQLException ex){
					log.error("",ex);
				}
			}
			throw new SQLException();
		}
		finally{
			DbUtils.close(statement);
			if(autoCommit){
				DbUtils.close(connection);
			}
		}
	}


	public  void executeQuery (
			String sql,boolean isgetTotalNum)
					throws Exception{

		Statement statement = null;
		PreparedStatement p_statement = null;
		ResultSet rs = null;
		if(sql == null){
			throw new Exception("in executeQuery function" +
					"sql is null");
		}
		try{
			if(isgetTotalNum){
				statement = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			}else{
				statement = connection.createStatement();
			}
			rs = statement.executeQuery(sql);
			if(isgetTotalNum){
				rs.last();
				log.debug("total row num is-->["+rs.getRow()+"]");
				rs.first();
			}
			ResultSetMetaData rsmd = rs.getMetaData();
			int numCols = rsmd.getColumnCount();
			log.debug("column num is-->["+rsmd.getColumnCount()+"]");

			while(rs.next()){
				for(int i=1;i<=numCols;i++){
					System.out.println("column " + i + " is-->["
							+ rs.getString(i) + "]");
				}
			}
		}catch( SQLException e){
			log.error("error sql is-->["+sql+"]");
			log.error("[ex in executeQuery function]-->", e);
			if(!autoCommit){
				try{
					if(log.isDebugEnabled()){
						log.debug("[in executeQuery function rollback]");
					}
					connection.rollback();
				}catch(SQLException ex){
					log.error("", ex);
				}
			}
			throw new SQLException();
		}
		finally{
			DbUtils.close(rs);
			DbUtils.close(statement);
			if(autoCommit){
				DbUtils.close(connection);
			}
		}
	}
}
